﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace loan_process
{
    public partial class ExcelParser
    {
        private static Excel.Workbook workbook = null;
        private static Excel.Application application = null;
        private static Excel.Worksheet sheet = null;
        private static Excel.Range range = null;
        private static int rowCount = 0;
        private static int colCount = 0;


        public ExcelParser()
        {
            application = new Excel.Application();
            application.Visible=false;
            workbook = application.Workbooks.Open(@"C:\\Users\\pmuthukum\\Documents\\Personal\\IOBLOAN.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            sheet = (Excel.Worksheet)workbook.Sheets[1];
            range = sheet.UsedRange;
            rowCount = range.Rows.Count;
            colCount = range.Columns.Count;
        }


        public void displaydata()
        {
            List<string> loanFile = new List<string>();
            string header = string.Empty;
            header = "DATE|Narration|FromDate|ToDate|Debit|Credit|Balance|TotalDays";

            loanFile.Add(header);
            
            for (int i = 2; i <= rowCount; i++)
            {
                LoanData data = new LoanData();
                IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);

                data.date = DateTime.Parse(range.Cells[i, 1].Value.ToString(), culture, System.Globalization.DateTimeStyles.AssumeLocal);
                data.narration = range.Cells[i,2].Value.ToString();

                if(data.narration.StartsWith("Normal Interest from"))
                {
                    string n = data.narration.Replace("Normal Interest from ","");
                    data.fromDate = DateTime.Parse(n.Substring(0, n.IndexOf(" to ")), culture, System.Globalization.DateTimeStyles.AssumeLocal);
                    data.toDate = DateTime.Parse(n.Substring(n.IndexOf(" to ") + 3).Trim(), culture, System.Globalization.DateTimeStyles.AssumeLocal);
                }
                data.credit = range.Cells[i, 3].Value;
                data.debit = range.Cells[i, 4].Value;
                data.balance = range.Cells[i, 5].Value;

                string finalData = data.ToString();
                finalData += "|" + data.difference();
                loanFile.Add(finalData);
           }

            using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\\Users\\pmuthukum\\Documents\\Personal\\IOBLOAN.txt"))
            {
                loanFile.ForEach(delegate(string d)
                {
                    file.WriteLine(d);
                });
            }
            workbook.Close(false, null, null);
            application.Quit();          

                
        }


        public static void Main(string[] args)
        {
            ExcelParser e = new ExcelParser();
            e.displaydata();
            
        }
    }
}
